import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import os
import shutil
import time
import datetime
import getpass
import pymysql
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']  #支持图中的中文显示

#功能：按产品显示3T工作量


def product_testhours_chart(startdate,product_list,limit):
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
    cursor = conn.cursor()

    #先取出满足条件的产品列表（根据在指定日期之后是否做过首单ORT,即是否已经在该日期后量产）
    cursor.execute('''SELECT product, SUM(test_hour*coefficient)  FROM assignment WHERE special !='s' and lab_engineer='张永庆' AND  vintage='1stORT' AND DATE>%s 
GROUP BY product  ORDER BY SUM(test_hour*coefficient) desc''',startdate)
    products=cursor.fetchall()
    for i in products:
        product_list.append(i[0])
    # print(product_list)
    
    #上面只是取出了做过首单ORT的产品，工时并没有意义
    #把取出的产品按总工时从大到小排序，所以要重新先取出各个产品的总工时.
    temp_dict={}    
    product_list_ordered=[]
    test_hours_ordered=[]
    for product in product_list:
        cursor.execute('''SELECT SUM(test_hour*coefficient) FROM assignment WHERE product=%s and special !='s' and lab_engineer!='张永庆' ''', product)
        workload=cursor.fetchone()
        if workload[0]>limit:
            temp_dict[product]=int(workload[0])  #工时太少<500的可能是单独的小SKU，忽略掉
    temp_dict_ordered=sorted(temp_dict.items(),key=lambda x:x[1],reverse=True)  #把字典按值排序，如果是按键排序，则为x[0],排序后变成列表
    print(temp_dict_ordered)
 
    fullalt=[]
    alteval=[]
    dvt=[]
    dvteval=[]
    carriers=[]
    fcnt=[]
    others=[]
    product_names=[] #放排序后的产品名，用于作图

    for i in temp_dict_ordered:
        # print(i[0],i[1])  #i[0]是产品名,i[1]是工时
        product_names.append(i[0])
        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category='Full ALT' and special !='s' 
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆' ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            fullalt.append(summary[0])
        else:
            fullalt.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category='ALT Eval' and special !='s' 
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆'  ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            alteval.append(summary[0])
        else:
            alteval.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category='DVT' and special !='s' 
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆' ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            dvt.append(summary[0])
        else:
            dvt.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category='DVT Eval' and special !='s'
         and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆'  ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            dvteval.append(summary[0])
        else:
            dvteval.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and (test_category='ATT' or test_category='TMO' or test_category='Softbank'  or test_category='Docomo') and special !='s' 
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆'  ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            carriers.append(summary[0])
        else:
            carriers.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category='FCNT' and special !='s'
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆'  ''', i[0])
        summary=cursor.fetchone()
        if summary[0] is not None:
            fcnt.append(summary[0])
        else:
            fcnt.append(0)

        cursor.execute('''select sum(test_hour*coefficient) from assignment where product=%s and test_category!='Full ALT' and test_category!='ALT Eval' and test_category !='DVT' and test_category !='DVT Eval' and 
        test_category!='ATT' and test_category!='TMO' and test_category !='Softbank' and test_category !='Docomo' and test_category !='FCNT' and special !='s' 
        and (lab_engineer='陈凯' or lab_engineer='汪焕' or lab_engineer='赵俊凯' or lab_engineer='熊春强') and lab_engineer!='张永庆' ''', i[0])
        summary=cursor.fetchone()    
        if summary[0] is not None:   
            others.append(summary[0])
        else:
            others.append(0)
     


    y7=fullalt  #fullalt是一个列表，里面的元素是不同月份的工时数
    y6=alteval
    y5=dvt
    y4=dvteval
    y3=carriers
    y2=fcnt
    y1=others

   

    d3=[]
    d4=[]
    d5=[]
    d6=[]
    d7=[]
    d8=[]



    for i in range(0, len(y1)):    
        sum3 = y1[i] + y2[i]
        sum4 = y1[i] + y2[i] +y3[i]
        sum5 = y1[i] + y2[i] +y3[i]+y4[i]
        sum6 = y1[i] + y2[i] +y3[i]+y4[i]+y5[i]
        sum7 = y1[i] + y2[i] +y3[i]+y4[i]+y5[i]+y6[i]
        sum8 = y1[i] + y2[i] +y3[i]+y4[i]+y5[i]+y6[i]+y7[i]



        d3.append(sum3)
        d4.append(sum4)
        d5.append(sum5)
        d6.append(sum6)
        d7.append(sum7)
        d8.append(sum8)


    # 2. 创建画布
    fig = plt.figure(figsize=(7, 5), dpi=100)
    ax = fig.add_subplot(111)

    # 3. 绘制柱形图，每个x对应的柱状图，y1是最底下的值，y2是第二节的值，bottom代表它下面是谁，以此类推
    ax.bar(product_names, y1, width=0.5, color='C3', label='Others')
    ax.bar(product_names, y2, width=0.5, bottom=y1, color='C4', label='FCNT')
    ax.bar(product_names, y3, width=0.5, bottom=d3, color='C5', label='Carriers')  
    ax.bar(product_names, y4, width=0.5, bottom=d4, color='C6', label='DVT_Eval')
    ax.bar(product_names, y5, width=0.5, bottom=d5, color='C7', label='DVT')
    ax.bar(product_names, y6, width=0.5, bottom=d6, color='C8', label='ALT_Eval')
    ax.bar(product_names, y7, width=0.5, bottom=d7, color='C9', label='Full_ALT')

    # 4. 设置样式
    ax.grid(axis='y', linestyle='--')    
    ax.set_axisbelow(True)
    ax.spines[['right', 'top']].set_color('C7')
    # ax.legend(fontsize=8,ncol=1, loc='upper left',bbox_to_anchor=(0.90, 0.95))  #bbox_to_anchor=(num1, num2), num1=0对应左边轴，1对应右边轴，num2对应上下方向的位置， 这个参数起作用后，loc可能就不管用了

    # 修改为反转图例顺序
    handles, labels = ax.get_legend_handles_labels()
    ax.legend(handles[::-1], labels[::-1], fontsize=8, ncol=1, loc='upper left', bbox_to_anchor=(0.90, 0.95))


    plt.xticks(fontsize=9,rotation=35)
    plt.ylabel('Hours')
    plt.title('TCC Reliability New Product Test Hours',fontsize = 13)
    plt.show()


if __name__=="__main__": 
    startdate='2025-01-01'  #定义1st ORT 在此时间之后的产品 
    limit=200 #实验工时limit设置，低于此工时的不显示在图中。实际执行时可以先设置在500，然后根据需要去掉哪些产品来确定一个工时限额
    product_list=[]  #定义额外需添加的产品
    # product_list=['Glory','Aura','Webb']   #定义额外需添加的产品，用于对比的目的，可以是startdate之前已经量产的产品
    #ip_address='localhost'
    ip_address='10.114.183.55'
 
    product_testhours_chart(startdate,product_list,limit)

    # 程序功能：生成3T阶段新产品工时汇总信息，可以添加额外产品，可以通过设定工时去掉非自研产品
    # 在定义时间的基础上，在此时间节点之后，根据首单ORT是否已经做过，来选择该展示的产品，
    #可以手动添加指定产品来作为对比的目的。可以是startdate之前已经量产的产品